package zozopo.operations.oa;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import zozopo.comm.CNSpell;
import zozopo.comm.ZOZOPOCODE;
import zozopo.database.BusiDatabaseManager;
import zozopo.database.SqlMachine;
import zozopo.operations.comm.DictionaryOS;
import zozopo.operations.comm.OperatingService;
import zozopo.warehouse.operations.GoodsTB;
import zozopo.warehouse.operations.PurchaseTB;
import zozopo.warehouse.operations.ReceiptlistTB;

public class OutlayOS extends OperatingService{
	private static final Log logger = LogFactory.getLog(OutlayOS.class);
	
	public OutlayOS(Connection conn){
		super(conn);
	}
	
	public OutlayOS(){
		this.databaseConnection = new BusiDatabaseManager();
		this.conn = databaseConnection.getConnection();
	}
	
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean insert(OutlayTB outlay){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> insertMap = new HashMap<String, Object>();

		insertMap.put(OutlayTB.KIND_OURLAY, outlay.getKind_outlay());
		insertMap.put(OutlayTB.REMARK_OURLAY, outlay.getRemark_outlay());
		insertMap.put(OutlayTB.TOTAL_OURLAY, outlay.getTotal_outlay());
		
		insertMap.put(OutlayTB.USEREMAIL_OURLAY, outlay.getUseremail_outlay());
		insertMap.put(OutlayTB.USERNAME_OURLAY, outlay.getUsername_outlay());
		
		return sqlMachine.insertRow(OutlayTB.TABLE_NAME, insertMap);
	}
	
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public OutlayTB getNew(String email){
		String sql = "select * from "+OutlayTB.TABLE_NAME
		+ " WHERE " + OutlayTB.USEREMAIL_OURLAY + "='"+email+"' "
		
		+" order by  "+OutlayTB.DATE_OURLAY
		+" desc limit 0,1"; 
		List<OutlayTB> list = selectListBySql(sql);
		OutlayTB outlay = list.get(0);
		
		
		return outlay;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<OutlayTB> selectListBySql(String sql){
		List<OutlayTB> list = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		DictionaryOS dictionaryOS= new DictionaryOS(conn);
		if(resultSet != null){
				try {
					list = new ArrayList<OutlayTB>();
					while(resultSet.next()){
						OutlayTB outlay = new OutlayTB();
						
						outlay.setDate_outlay(resultSet.getString(OutlayTB.DATE_OURLAY));
						outlay.setId_outlay(resultSet.getInt(OutlayTB.ID_OURLAY));
						outlay.setKind_outlay(resultSet.getString(OutlayTB.KIND_OURLAY));
						outlay.setRemark_outlay(resultSet.getString(OutlayTB.REMARK_OURLAY));
						outlay.setState_outlay(resultSet.getString(OutlayTB.STATE_OURLAY));
						outlay.setTotal_outlay(resultSet.getDouble(OutlayTB.TOTAL_OURLAY));
						outlay.setUseremail_outlay(resultSet.getString(OutlayTB.USEREMAIL_OURLAY));
						outlay.setUsername_outlay(resultSet.getString(OutlayTB.USERNAME_OURLAY));
						
						
						//goodskindvalue_outlay
						outlay.setKindvalue_outlay(dictionaryOS.getDictValue(outlay.getKind_outlay()));//为了显示

				
						list.add(outlay);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return list;
	
	}
	
	public List<OutlayTB> select(int beginPageNum,int pageSize){
		
		List<OutlayTB> list = null;
		String sql = "SELECT * FROM " + OutlayTB.TABLE_NAME  +" ORDER BY "+OutlayTB.ID_OURLAY+" DESC limit "+beginPageNum+","+pageSize; 
		list = selectListBySql(sql);
		
		return list;
	}
	
	public List<OutlayTB> selectBySearchStr(String searchStr,int beginPageNum,int pageSize){
		
		List<OutlayTB> list = null;
		String sql = "SELECT * FROM " + OutlayTB.TABLE_NAME  +" ORDER BY "+OutlayTB.ID_OURLAY+" DESC limit "+beginPageNum+","+pageSize; 
		list = selectListBySql(sql);
		
		return list;
	}
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNum(){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + OutlayTB.TABLE_NAME ;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + ReceiptlistTB.TABLE_NAME +" WHERE  "
		+ReceiptlistTB.ID_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.CUSTOMERID_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.CUSTOMERINFO_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.DATE_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		
		+" OR "+ReceiptlistTB.DEPARTMENT_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.REMARK_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.TOTAL_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.USERNAME_RECEIPTLIST+" LIKE '%"+searchStr+"%'";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	public OutlayTB getOutlayById(int id){
		
		String sql = "SELECT * FROM " + OutlayTB.TABLE_NAME + " where " + OutlayTB.ID_OURLAY + "=" + id;
		OutlayTB outlay = new OutlayTB();
		List<OutlayTB> list = selectListBySql(sql);
		outlay = list.get(0);
		return outlay;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean cancel(int id){
		SqlMachine sqlMachine = new SqlMachine(conn);
		
		//OutlayTB outlay = getOutlayById(id);
		
		
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(OutlayTB.STATE_OURLAY, ZOZOPOCODE.CANCELED);
		
		return sqlMachine.updateRow(OutlayTB.TABLE_NAME, updateMap, OutlayTB.ID_OURLAY, id);
	}
	
}
